package com.nbgroup.service.Identity;


import com.nbgroup.dao.Identity.IdentityDao;
import com.nbgroup.entity.Identity.IdeantityAge;
import com.nbgroup.entity.Identity.Identity;
import com.nbgroup.entity.Identity.IdentityTest;
import com.nbgroup.entity.region.Province;
import com.nbgroup.utils.InputExcel;
import com.nbgroup.utils.PageUtil;
import com.nbgroup.utils.QueryUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by 顾志杰 on 2018/8/15.
 */
@Service
public class IdentityService {
    @Autowired
    InputExcel inputExcel;
    @Autowired
    IdentityDao identityDao;
    @Autowired
    QueryUtil queryUtil;

    /**
     * 查询身份列表
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/15 14:39
     */
    public PageUtil<Identity> findAllByPage(Identity identity, Integer page, Integer pageSize) {
        StringBuffer sql = new StringBuffer();
        //排空，设置默认值
        if (null == page || "".equals(page) || "0".equals(page)) {
            page = 1;
        }
        //排空，设置默认值
        if (null == pageSize || "".equals(pageSize) || "0".equals(pageSize)) {
            pageSize = 10;
        }
        sql.append("select address,date_of_birth,name,nation,identity_id,sex,recent_date from identity where 1=1");
        //按身份证
        if (StringUtils.isNotBlank(identity.getIdentityId())) {
            sql.append(" and identity_id like '%" + identity.getIdentityId().trim() + "%' ");
        }
        //按名字
        if (StringUtils.isNotBlank(identity.getName())) {
            sql.append(" and name like '%" + identity.getName().trim() + "%'");
        }
        //按性别
        if (StringUtils.isNotBlank(identity.getSex())) {
            sql.append(" and sex = '" + identity.getSex() + "'");
        }
        //按年龄
        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        String dateString = formatter.format(date);
        if (StringUtils.isNotBlank(identity.getMinAge())) {
            sql.append(" and year('" + dateString + "')-year(date_of_birth)>=" + identity.getMinAge().trim());
        }
        if (StringUtils.isNotBlank(identity.getMaxAge())) {
            sql.append(" and year('" + dateString + "')-year(date_of_birth)<=" + identity.getMaxAge().trim());
        }
        //按民族
        if (StringUtils.isNotBlank(identity.getNation())) {
            sql.append(" and nation like '%" + identity.getNation().trim() + "%'");
        }
        //按住址
        if (StringUtils.isNotBlank(identity.getAddress())) {
            sql.append(" and address like '%" + identity.getAddress().trim() + "%'");
        }
        //按签发机关
        if (StringUtils.isNotBlank(identity.getAgencyUnit())) {
            sql.append(" and agency_unit like '%" + identity.getAgencyUnit() + "%'");
        }
        //按物理卡号
        if (StringUtils.isNotBlank(identity.getPhysicalCardNumber())) {
            sql.append(" and physical_card_number like '%" + identity.getPhysicalCardNumber() + "%'");
        }
        //按最早验证时间
        if (StringUtils.isNotBlank(identity.getMinEarliestDate())) {
            sql.append(" and earliest_date >= '" + identity.getMinEarliestDate() + "'");
        }
        if (StringUtils.isNotBlank(identity.getMaxEarliestDate())) {
            sql.append(" and earliest_date <= '" + identity.getMaxEarliestDate() + "'");
        }
        //按最近验证时间
        if (StringUtils.isNotBlank(identity.getMinRecentDate())) {
            sql.append(" and recent_date >= '" + identity.getMinRecentDate() + " 00:00:00'");
        }
        if (StringUtils.isNotBlank(identity.getMaxRecentDate())) {
            sql.append(" and recent_date <= '" + identity.getMaxRecentDate() + " 23:59:59'");
        }
        sql.append(" order by recent_date DESC");
        PageUtil<Identity> identityList = queryUtil.queryByPage(sql.toString(), Identity.class, page, pageSize);
        return identityList;
    }

    /**
     * 批量导入身份信息
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/15 14:36
     */
    public List<String> input(MultipartFile mFile) {
        List<String[]> list = inputExcel.getExcelList(mFile);
        List<Identity> identityList = new ArrayList<>();
        List<String> errorList = verificationExcelList(list);
//        List<String> errorList = new ArrayList<>();
        if (errorList.size() != 0) {
            return errorList;
        }
        Calendar calendar = new GregorianCalendar(1900, 0, -1);
        Date d = calendar.getTime();
        int num = 0;
        for (int i = 0; i < list.size(); i++) {
            if (i == 0) {
                continue;
            } else {
                String time=list.get(i)[6];//出生日期
                String aa = list.get(i)[1];//核验时间

                Date date1=null;
                SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy年MM月dd日");
//                SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");

                try {
                    if (time.matches("\\d{4}年\\d{1,2}月\\d{1,2}日")){
                        date1=simpleDateFormat.parse(time);
                    }else{
                        date1=new Date();
                        errorList.add("出生日期格式错误，以变为当前系统时间！");
                    }

//                    date=formatter.parse(time);
//                    errorList.add("导入失败，出生日期格式不正确");
                    System.out.println(date1);
                } catch (ParseException e) {
                    System.out.println("------------------");
                    System.out.println(aa);
                    e.printStackTrace();
                }
                Identity identity = new Identity();
                identity.setIdentityId(list.get(i)[2]);//身份证
                identity.setName(list.get(i)[3]);//姓名
                identity.setSex(list.get(i)[4]);//性别
                identity.setNation(list.get(i)[5]);//民族

                identity.setDateOfBirth(date1);//出生日期
//                identity.setNation(list.get(i)[6]);
                identity.setAddress(list.get(i)[7]);//地址
                identity.setAgencyUnit(list.get(i)[8]);//签发机关
                identity.setPhysicalCardNumber(list.get(i)[0]);//设备号
                identity.setRecentDate(list.get(i)[1]);//核验时间
                identityList.add(identity);
                num++;
            }


        }
        identityDao.save(identityList);
        errorList.add("导入成功，一共导入" + num + "条");
        return errorList;
    }

    /**
     * 验证数据表格内容有没有空字段
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/16 9:15
     */
    public List<String> verificationExcelList(List<String[]> list) {
        List<String> errorList = new ArrayList<>();
        try {
            if (!"设备号".equals(list.get(0)[0]) && !"核验时间".equals(list.get(0)[1]) && !"公民身份号码".equals(list.get(0)[4]) && !"姓名".equals(list.get(0)[5])  && !"性别".equals(list.get(0)[7]) && !"民族/国籍".equals(list.get(0)[8]) && !"出生年月".equals(list.get(0)[9]) && !"地址".equals(list.get(0)[10])  && !"签发机关".equals(list.get(0)[13])) {
                errorList.add("导入模版不正确");
                return errorList;
            }
        }catch (Exception e){
             errorList.add("导入模版不正确");
             return errorList;
        }

        for (int i = 0; i < list.size(); i++) {
            if (i == 0) {
                continue;
            } else {
                String error = "";
                try {
                    if ("".equals(list.get(i)[0])) {
                        error += "第" + i + "行A列";
                    }
                    if ("".equals(list.get(i)[1])) {
                        if ("".equals(error)) {
                            error += "第" + i + "行B列";
                        } else {
                            error += ",B列";
                        }
                    }
                    if ("".equals(list.get(i)[5])) {
                        if ("".equals(error)) {
                            error += "第" + i + "行F列";
                        } else {
                            error += ",F列";
                        }
                    }
                    if ("".equals(list.get(i)[8])) {
                        if ("".equals(error)) {
                            error += "第" + i + "行I列";
                        } else {
                            error += ",I列";
                        }
                    }
//                    if ("".equals(list.get(i)[9])) {
//                        if ("".equals(error)) {
//                            error += "第" + i + "行J列";
//                        } else {
//                            error += ",J列";
//                        }
//                    }
//                    if ("".equals(list.get(i)[10])) {
//                        if ("".equals(error)) {
//                            error += "第" + i + "行K列";
//                        } else {
//                            error += ",K列";
//                        }
//                    }
//                    if ("".equals(list.get(i)[13])) {
//                        if ("".equals(error)) {
//                            error += "第" + i + "行N列";
//                        } else {
//                            error += ",N列";
//                        }
//                    }
                    if ("".equals(error)) {
                        continue;
                    } else {
                        error += "数据为空";
                        errorList.add(error);
                    }
               }catch (Exception e){
                   errorList.add("第"+i+"行数据异常");
                   continue;
               }


            }
        }
        return errorList;
    }

    /**
     * 获取单独身份
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/15 15:11
     */
    public Identity findOne(String id) {
        return identityDao.findOne(id);
    }

    /**
     * 查询个省客流量统计
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/19 11:56
     */
    public List<Province> findProvinceCount() {
        String sql = "select p.region_name,IFNULL(i.count,0)count from province p LEFT JOIN \n" +
                "(select identity_id,COUNT(*) count FROM identity GROUP BY SUBSTR(identity_id FROM 1 FOR 2))\n" +
                " i on  SUBSTR(i.identity_id FROM 1 FOR 2)=p.code\n" +
                "GROUP BY p.region_name";
        return queryUtil.query(sql, Province.class);
    }

    public List<Province> findProvinceCountByTime(String start ,String end) {
        String sql = "select p.region_name,IFNULL(i.count,0)count from province p LEFT JOIN \n" +
                "(select identity_id,COUNT(*) count FROM identity WHERE recent_date > '"+start+"' AND recent_date < '"+end+"' GROUP BY SUBSTR(identity_id FROM 1 FOR 2))\n" +
                " i on  SUBSTR(i.identity_id FROM 1 FOR 2)=p.code\n" +
                "GROUP BY p.region_name";
        return queryUtil.query(sql, Province.class);
    }
    //用于APP
    public List<Province> findProvinceCountByTime2(String start ,String end) {
        String sql = "select p.region_name,IFNULL(i.count,0)count from province p LEFT JOIN \n" +
                "(select identity_id,COUNT(*) count FROM identity WHERE recent_date > '"+start+"' AND recent_date < '"+end+"' GROUP BY SUBSTR(identity_id FROM 1 FOR 2))\n" +
                " i on  SUBSTR(i.identity_id FROM 1 FOR 2)=p.code\n" +
                "GROUP BY p.region_name ORDER BY  count DESC LIMIT 5";
        return queryUtil.query(sql, Province.class);
    }
    public List<Province> findProvinceCountByTime3(String start ,String end) {
        String sql = "select p.region_name,IFNULL(i.count,0)count from province p LEFT JOIN \n" +
                "(select identity_id,COUNT(*) count FROM identity WHERE recent_date > '"+start+"' AND recent_date < '"+end+"' GROUP BY SUBSTR(identity_id FROM 1 FOR 2))\n" +
                " i on  SUBSTR(i.identity_id FROM 1 FOR 2)=p.code\n" +
                "GROUP BY p.region_name ORDER BY  count DESC LIMIT 50";
        return queryUtil.query(sql, Province.class);
    }
    //app男女比例
    public List<IdentityTest> testMans(String start , String end){
        String sql = "select  \n" +
                "count(*) as count,\n" +
                "sum(case when sex=1 then 1 else 0 end) man,\n" +
                "round(sum(case when sex=1 then 1 else 0 end)*100 /count(*)) manp,\n" +
                "sum(case when sex=2 then 1 else 0 end) woman,\n" +
                "round(sum(case when sex=2 then 1 else 0 end)*100 /count(*)) womanp  \n" +
                "from identity\n" +
                "WHERE recent_date > '"+start+"'  AND recent_date < '"+end+"' ";
        System.out.println("__________________");
        System.out.println(sql);
        System.out.println("-————————————————————");
        return queryUtil.query(sql,IdentityTest.class);
    }
    public String[] arrayProvince1(List<IdentityTest> list) {
        String str = "";
        for(IdentityTest identityTest:list){
            str += identityTest.getMan() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }
    public String[] arrayCount1(List<IdentityTest> list) {
        String str = "";
        for(IdentityTest identityTest:list){
            str += identityTest.getWoman() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }


    /**
     * 未来一个月的客流量
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/20 9:38
     */
    public long[] findFutureOneMonthCount() {
        Date date = new Date();
        String between = "";
        String and = "";
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.YEAR, -1);
        calendar.add(Calendar.MONTH, 1);
        between = format.format(calendar.getTime());
        calendar.add(Calendar.MONTH, 1);
        and = format.format(calendar.getTime());
        String sql = "select COUNT(*) count from identity where recent_date >=\"" + between + "\" and recent_date<=\"" + and + "\"";
        List<Province> provinceList = queryUtil.query(sql, Province.class);
        long num[] = new long[6];
        //未来一个月的
        num[0] = Long.parseLong(provinceList.get(0).getCount());
        //未来两个月的
        calendar.add(Calendar.MONTH, 1);
        between = format.format(calendar.getTime());
        sql = "select COUNT(*) count from identity where recent_date >=\"" + and + "\" and recent_date<=\"" + between + "\"";
        num[1] = Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
        //未来三个月的
        calendar.add(Calendar.MONTH, 1);
        and = format.format(calendar.getTime());
        sql = "select COUNT(*) count from identity where recent_date >=\"" + between + "\" and recent_date<=\"" + and + "\"";
        num[2] = Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
        //未来四个月的
        calendar.add(Calendar.MONTH, 1);
        between = format.format(calendar.getTime());
        sql = "select COUNT(*) count from identity where recent_date >=\"" + and + "\" and recent_date<=\"" + between + "\"";
        num[3] = Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
        //未来五个月的
        calendar.add(Calendar.MONTH, 1);
        and = format.format(calendar.getTime());
        sql = "select COUNT(*) count from identity where recent_date >=\"" + between + "\" and recent_date<=\"" + and + "\"";
        num[4] = Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
        //未来六个月的
        calendar.add(Calendar.MONTH, 1);
        between = format.format(calendar.getTime());
        sql = "select COUNT(*) count from identity where recent_date >=\"" + and + "\" and recent_date<=\"" + between + "\"";
        num[5] = Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
        return num;
    }

    /**
     * 当前客流量
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/20 9:38
     */
    public Long findCurrentCount(String data) {
        Date date = new Date();
        String str = "";
        if (data.equals("D")) {
            //当天
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            str = format.format(date);
        } else if (data.equals("Y")) {
            //当月
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
            str = format.format(date);
        } else {
            //当年
            SimpleDateFormat format = new SimpleDateFormat("yyyy");
            str = format.format(date);
        }
        String sql = "select COUNT(*) count from identity where recent_date like '" + str + "%'";
        if (data.equals("W")) {
            //本周
            sql = "select COUNT(*) count from identity where WEEKOFYEAR(recent_date)=WEEKOFYEAR(now()); ";
        }
        return Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
    }

    /**
     * 月度客流量统计
     *
     * @param
     * @author 顾志杰
     * @date 2018/8/22 9:19
     */
    public Long findMonthCount() {
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //当前时间
        String str = format.format(date);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, -30);
        //当前时间减去30天的时间
        String sts = format.format(calendar.getTime());
        String sql = "select count(*) count from identity ";
        return Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
    }
    public Long findMonthCountByTime(String start ,String end) {
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //当前时间
        String str = format.format(date);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, -30);
        //当前时间减去30天的时间
        String sts = format.format(calendar.getTime());
        String sql = "select count(*) count from identity where recent_date >='" + start + "' and recent_date <='" + end + "'";
        return Long.parseLong(queryUtil.query(sql, Province.class).get(0).getCount());
    }

    /**
     * 根据性别查询人数
     *
     * @param sex 性别
     * @return 数量
     */
    public Long count(String sex) {
        return identityDao.countBySex(sex);
    }
    public Long countByTime(String sex,String startTime ,String endTime) {
        return identityDao.countBySexAndRecentDateBetween(sex,startTime,endTime);
    }

    public Long count(Integer start, Integer end) {
        return identityDao.countByAge(start, end);
    }
    public Long countByTime(Integer start, Integer end,String startTime ,String endTime) {
        String sql = "SELECT * FROM identity WHERE( TIMESTAMPDIFF( YEAR, date_of_birth, curdate() ) BETWEEN "+start+" AND "+end+")AND recent_date BETWEEN '"+startTime+"' AND '"+endTime+"'";
        List<Identity> query = queryUtil.query(sql, Identity.class);
        return (long) query.size();
    }


    public void addPeople(Identity identity){
        identityDao.save(identity);
    }
    public String findOneByIdentityId(){
        String sql = "SELECT i.identity_id FROM identity i ORDER BY i.recent_date DESC LIMIT 1";
        return queryUtil.query(sql,Identity.class).get(0).getIdentityId();
    }


    /**
     * 统计分析 客流量统计
     * 2018年9月10日, PM 03:49:36
     *
     * @author 姜文
     */
    public List<Province> findProvinceStatisticsForSql() {
        String sql = "SELECT\n" +
                "\tp.region_name,\n" +
                "\tIFNULL(i.count, 0) count,\n" +
                "\ti. CODE\n" +
                "FROM\n" +
                "\tprovince p\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tidentity_id,\n" +
                "\t\tSUBSTR(identity_id FROM 1 FOR 2) AS CODE,\n" +
                "\t\tCOUNT(*) count\n" +
                "\tFROM\n" +
                "\t\tidentity\n" +
                "\tGROUP BY\n" +
                "\t\tSUBSTR(identity_id FROM 1 FOR 2)\n" +
                ") i ON SUBSTR(i.identity_id FROM 1 FOR 2) = p. CODE\n" +
                "GROUP BY\n" +
                "\tp.region_name\n" +
                "HAVING\n" +
                "\tcount <> 0\n" +
                "ORDER BY\n" +
                "\tcount DESC\n" +
                "LIMIT 6";
        return queryUtil.query(sql, Province.class);
    }
    public List<Province> findProvinceStatisticsForSqlByTime(String start ,String end) {
        String sql = "SELECT\n" +
                "\tp.region_name,\n" +
                "\tIFNULL(i.count, 0) count,\n" +
                "\ti. CODE\n" +
                "FROM\n" +
                "\tprovince p\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tidentity_id,\n" +
                "\t\tSUBSTR(identity_id FROM 1 FOR 2) AS CODE,\n" +
                "\t\tCOUNT(*) count\n" +
                "\tFROM\n" +
                "\t\tidentity \tWHERE recent_date >'"+start+"' AND recent_date <'"+end+"' \n" +
                "\tGROUP BY\n" +
                "\t\tSUBSTR(identity_id FROM 1 FOR 2)\n" +
                ") i ON SUBSTR(i.identity_id FROM 1 FOR 2) = p. CODE\n" +
                "GROUP BY\n" +
                "\tp.region_name\n" +
                "HAVING\n" +
                "\tcount <> 0\n" +
                "ORDER BY\n" +
                "\tcount DESC\n" +
                "LIMIT 6";
        return queryUtil.query(sql, Province.class);
    }

    /**
     * 统计分析 客流量统计--男女
     * 2018年9月10日, PM 03:49:36
     *
     * @author 姜文
     */
    public List<Identity> findProvinceStatisticsForSex(String sex, String code) {
        String sql = "SELECT identity_id, sex, COUNT(id) AS count FROM identity WHERE sex = '"+sex+"' AND SUBSTR(identity_id FROM 1 FOR 2) = '"+code+"'";
        return queryUtil.query(sql, Identity.class);
    }
    public List<Identity> findProvinceStatisticsForSexByTime(String sex, String code,String start ,String end) {
        String sql = "SELECT identity_id, sex, COUNT(id) AS count FROM identity WHERE sex = '"+sex+"' AND SUBSTR(identity_id FROM 1 FOR 2) = '"+code+"' AND recent_date >'"+start+"' AND recent_date <'"+end+"' ";
        return queryUtil.query(sql, Identity.class);
    }
    /**
     * 统计分析 客流量统计--省份
     * 2018年9月10日, PM 03:49:36
     *
     * @author 姜文
     */
    public String[] findProvinceStatistics() {
        List<Province> provinceList = findProvinceStatisticsForSql();
        String str = "";
        for(Province province:provinceList){
            str += province.getRegionName() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }
    public String[] findProvinceStatisticsByTime(String start ,String end) {
        List<Province> provinceList = findProvinceStatisticsForSqlByTime(start,end);
        String str = "";
        for(Province province:provinceList){
            str += province.getRegionName() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }
    public int[] findProvinceStatisticsForSexByTime(String sex,String start ,String end) {
        List<Province> provinceList = findProvinceStatisticsForSqlByTime(start,end);
        int[] intArray = new int[provinceList.size()];
        for(int i=0;i<provinceList.size();i++){
            Identity identity = findProvinceStatisticsForSexByTime(sex, provinceList.get(i).getCode(),start,end).get(0);
            intArray[i] = identity.getCount();
        }
        return intArray;
    }

    /**
     * 统计分析 客流量统计--省份
     * 2018年9月10日, PM 03:49:36
     *
     * @author 姜文
     */
    public int[] findProvinceStatisticsForSex(String sex) {
        List<Province> provinceList = findProvinceStatisticsForSql();
        int[] intArray = new int[provinceList.size()];
        for(int i=0;i<provinceList.size();i++){
            Identity identity = findProvinceStatisticsForSex(sex, provinceList.get(i).getCode()).get(0);
            intArray[i] = identity.getCount();
        }
        return intArray;
    }

//    /**
//     * 统计分析 客流量统计--省份
//     * 2018年9月10日, PM 03:49:36
//     *
//     * @author 姜文
//     */
//    public String findProvinceStatisticsForWeman() {
//        List<Province> provinceList = findProvinceStatisticsForSql();
//        String str = "";
//        for(Province province:provinceList){
//            List<Identity> identityList = findProvinceStatisticsForSex("女", province.getCount());
//            Identity identity = identityList.get(0);
//            str += identity.getCount().toString();
//        }
//        return str;
//    }

    public String[] arrayProvince(List<Province> list) {
        String str = "";
        for(Province province:list){
            str += province.getRegionName() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }
    public String[] arrayCount(List<Province> list) {
        String str = "";
        for(Province province:list){
            str += province.getCount() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }

    /**
     * app火灾馆数据 年龄比例
     *
     * @author lhl
     */
    public List<IdeantityAge> ager(String start ,String end){
        String sql = "SELECT nnd as ageRange,count(*) as count from(\n" +
                "   SELECT\n" +
                "      CASE\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 0\n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 12 THEN '0-12岁'\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 13 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 17 THEN '13-17岁'\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 18 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 22 THEN '18-22岁'\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 23\n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 30 THEN '23-30岁'\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 31 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 59 THEN '31-59岁'\n" +
                "         WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 60 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 69 THEN '60-69岁'\n" +
                "\t\t\t\t\t WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 70 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 79 THEN '70-79岁'\t \n" +
                "\t\t\t\t WHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 80 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 89 THEN '80-89岁'\n" +
                "\t\tWHEN ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) >= 90 \n" +
                "             and ROUND(DATEDIFF(CURDATE(), date_of_birth)/365.2422) <= 100 THEN '90-100岁'\t \t\t\t\t \n" +
                "      END as nnd\n" +
                "     \n" +
                "   from identity\n" +
                "\t WHERE recent_date > '"+start+"'  AND recent_date < '"+end+"'\n" +
                ") a GROUP BY nnd";
        System.out.println("++++++++++++++++");
        System.out.println(sql);
        return queryUtil.query(sql,IdeantityAge.class);
    }

    public String[] arrayProvince2(List<IdeantityAge> list) {
        String str = "";
        for(IdeantityAge IdeantityAge:list){
            str += IdeantityAge.getCount() + ",";
        }
        str = str.substring(0, str.length()-1);
        String[] strArray = str.split(",");
        return strArray;
    }

}
